import pandas as pd
import numpy as np
import pymysql

if __name__ == '__main__':
    conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='transportationdata', charset='utf8')
    # 车牌过滤
    plateData = pd.read_sql("select * from plate_table where plate_num!='-'", con=conn)
    monitorData = pd.read_sql("select * from monitor_17_table", con=conn)

    data = plateData.merge(monitorData, on="monitor_id", how="left")
    data = data.dropna()

    monitorNum = len(monitorData)
    arr = np.zeros((monitorNum, monitorNum))

    # 根据车牌分组
    for name, group in data.groupby("plate_num"):
        # 高峰时间过滤
        group["pass_time"] = pd.to_datetime(group["pass_time"])
        # 选取时间为17：00之后的数据
        group = group[group["pass_time"].dt.hour.isin(np.arange(17, 24))]
        # 少于2条的数据不满足开始、结束的最低要求
        if len(group) < 2:
            continue

        # 选取时间为高峰时间的数据
        start = group[group["pass_time"].dt.hour.isin(np.arange(17, 18))]
        # 没有开始数据，不满足要求
        if len(start) == 0:
            continue
        # 选取最早时间
        startIdx = start["pass_time"].argmin()
        startMonitorNum = int(group.iloc[startIdx]["monitor_num"])

        # 选取的数据一定有结束数据
        end = group
        # 选取最晚时间
        endIdx = end["pass_time"].argmax()
        endMonitorNum = int(group.iloc[endIdx]["monitor_num"])

        arr[startMonitorNum, endMonitorNum] += 1
        print(group.head())

    print(arr)
    np.savetxt("res/FlowDirectionRes.txt", arr, fmt='%d')

